package com.liu.lab03.dao;

import com.liu.lab03.pojo.Music;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class MusicDao {
    private Connection conn;
    private PreparedStatement ps;
    private ResultSet rs;
    /**
     * 获取连接
     * @return
     */
    private Connection getConn(){
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/db_test";
            conn = DriverManager.getConnection(url, "root", "12345678");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 释放资源
     */
    private void close(){
        try {
            if (rs!=null){
                rs.close();
            }
            if (ps!=null){
                ps.close();
            }
            if (conn!=null){
                conn.close();
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
    }
    /**
     * 查询所有音乐信息
     */
    public List<Music> findAllMusics(){
       List<Music> musics=new ArrayList<Music>();
        try {
            conn = getConn();
            String sql = "SELECT * FROM tb_music";
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while(rs.next()) {
                Music m=new Music();
                m.setId(rs.getInt("id"));
                m.setMusicname(rs.getString("musicname"));
                m.setSinger(rs.getString("singer"));
                m.setRanking(rs.getInt("ranking"));
                m.setCategory_id(rs.getInt("category_id"));
                musics.add(m);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close();
        }
        return musics;
    }

    /**
     * 添加音乐信息
     */
    public int addMusic(Music s) {
        int rows = 0;
        try {
            conn = getConn();
            String sql = "INSERT INTO tb_music VALUES(NULL,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, s.getMusicname());
            ps.setString(2, s.getSinger());
            ps.setInt(3, s.getRanking());
            ps.setInt(4,s.getCategory_id());
            System.out.println(ps.toString());
            rows = ps.executeUpdate();
            close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rows;
    }

    /**
     * 根据ID查找音乐信息信息
     */
    public Music findMusicById(int id){
        Music m=null;
        try {
            conn = getConn();
            String sql = "SELECT * FROM tb_music where id="+id;
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            if (rs.next()) {
                m=new Music();
                m.setId(id);
                m.setMusicname(rs.getString("musicname"));
                m.setSinger(rs.getString("singer"));
                m.setRanking(rs.getInt("ranking"));
                m.setCategory_id(rs.getInt("category_id"));
            }
            close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return m;
    }

    /**
     * 修改音乐信息
     */
    public int updateMusic(Music s) {
        int rows = 0;
        try {
            conn = getConn();
            String sql = "update tb_music set musicname=?, singer=?, ranking=?,category_id=? where id=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, s.getMusicname());
            ps.setString(2, s.getSinger());
            ps.setInt(3, s.getRanking());
            ps.setInt(4,s.getCategory_id());
            ps.setInt(5,s.getId());
            // 打印SQL
            System.out.println("Prepared SQL: " + ps.toString());
            rows = ps.executeUpdate();
            System.out.println(" 这是修改音乐 "+rows);
            close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rows;
    }

    /**
     * 根据ID删除音乐信息信息
     */

    public int deleteMusic(int id) {
        Connection conn = null;
        PreparedStatement ps = null;
        int rows = 0;
        try {
            conn = getConn();
            String sql = "delete from tb_music where id="+id;
            ps = conn.prepareStatement(sql);
            //ps.setInt(1,id);
            rows = ps.executeUpdate();
            close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rows;
    }

    /*
    根据关键词查询音乐
     */
    public List<Music> findMusics(String key){
        List<Music> musics=new ArrayList<>();
        try {
            conn = getConn();
            String sql = "SELECT * FROM tb_music ";
            if (key!=null){
                sql+=" where musicname like '%"+key+"%'";
            }
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while(rs.next()) {
                Music m=new Music();
                m.setId(rs.getInt("id"));
                m.setMusicname(rs.getString("musicname"));
                m.setSinger(rs.getString("singer"));
                m.setRanking(rs.getInt("ranking"));
                m.setCategory_id(rs.getInt("category_id"));
                musics.add(m);
            }
            close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return musics;
    }


}
